﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace TakeoutMangesDAL
{
    public class ProductDAL
    {
        //查询所有数据
        public static List<Model.Product> getProduct(string Name)
        {
            string sqlstr = "select * from Product where Isdelete<>1";
            SqlParameter[] pams = new SqlParameter[Name != "" ? 1 : 0];
            if (Name != "")
            {
                sqlstr += " and ProductName like'%'+@Name+'%'";
                pams[0] = new SqlParameter("@Name", Name);
            }
            SqlDataReader reader = DBHelper.ExecuteReader(sqlstr, CommandType.Text, pams);
            List<Model.Product> list = new List<Model.Product>();
            int n = reader.FieldCount;
            while (reader.Read())
            {
                Model.Product p = new Model.Product()
                {
                    ProductID = Convert.ToInt32(reader["ProductID"]),
                    ProductName = reader["ProductName"].ToString(),
                    ProductImage = reader["ProductImage"].ToString(),
                    ProductPrice = reader["ProductPrice"].ToString(),
                    ProductSales = reader["ProductSales"].ToString(),
                    CategoryID = Convert.ToInt32(reader["CategoryID"]),
                    EnterpriseID = Convert.ToInt32(reader["EnterpriseID"]),
                    Isdelete = Convert.ToInt32(reader["Isdelete"]),
                };
                list.Add(p);
            }
            if (list != null)
                return list;
            return null;
        }

        //查询类别中商品个数
        public static List<Model.Product> Getcount(int id)
        {
            string sqlstr = "select * from Product where CategoryID=@id";
            SqlParameter[] pams = new SqlParameter[]
            {
                new SqlParameter("@id", id),
            };
            SqlDataReader reader = DBHelper.ExecuteReader(sqlstr, CommandType.Text, pams);
            List<Model.Product> list = new List<Model.Product>();
            int n = reader.FieldCount;
            while (reader.Read())
            {
                Model.Product p = new Model.Product()
                {
                    ProductID = Convert.ToInt32(reader["ProductID"]),
                    ProductName = reader["ProductName"].ToString(),
                    ProductImage = reader["ProductImage"].ToString(),
                    ProductPrice = reader["ProductPrice"].ToString(),
                    ProductSales = reader["ProductSales"].ToString(),
                    CategoryID = Convert.ToInt32(reader["CategoryID"]),
                    EnterpriseID = Convert.ToInt32(reader["EnterpriseID"]),
                    Isdelete = Convert.ToInt32(reader["Isdelete"]),
                };
                list.Add(p);
            }
            if (list.Count > 0)
                return list;
            return null;
        }

        //按类别查询,分页
        public static List<Model.Product> GetProductCategory(int id, int PageCount, int PageNumber)
        {
            string sqlstr = "select top(@PageCount)* from Product where ProductID not in(select top(@PageCount * (@PageNumber - 1))ProductID from Product where CategoryID=@id) and CategoryID=@id";
            SqlParameter[] pams = new SqlParameter[]
            {
                new SqlParameter("@id", id),
                new SqlParameter("@PageCount",PageCount),
                new SqlParameter("@PageNumber",PageNumber)
            };
            SqlDataReader reader = DBHelper.ExecuteReader(sqlstr, CommandType.Text, pams);
            List<Model.Product> list = new List<Model.Product>();
            int n = reader.FieldCount;
            while (reader.Read())
            {
                Model.Product p = new Model.Product()
                {
                    ProductID = Convert.ToInt32(reader["ProductID"]),
                    ProductName = reader["ProductName"].ToString(),
                    ProductImage = reader["ProductImage"].ToString(),
                    ProductPrice = reader["ProductPrice"].ToString(),
                    ProductSales = reader["ProductSales"].ToString(),
                    CategoryID = Convert.ToInt32(reader["CategoryID"]),
                    EnterpriseID = Convert.ToInt32(reader["EnterpriseID"]),
                    Isdelete = Convert.ToInt32(reader["Isdelete"]),
                };
                list.Add(p);
            }
            if (list.Count > 0)
                return list;
            return null;
        }
        //查询详细
        public static List<Model.Product> getProductinfo(int id)
        {
            string sqlstr = "select * from Product where Isdelete<>1 and ProductID=@id";
            SqlParameter[] pams = new SqlParameter[]
            {
                new SqlParameter("@id", id)
            };
            SqlDataReader reader = DBHelper.ExecuteReader(sqlstr, CommandType.Text, pams);
            List<Model.Product> list = new List<Model.Product>();
            int n = reader.FieldCount;
            while (reader.Read())
            {
                Model.Product p = new Model.Product()
                {
                    ProductID = Convert.ToInt32(reader["ProductID"]),
                    ProductName = reader["ProductName"].ToString(),
                    ProductImage = reader["ProductImage"].ToString(),
                    ProductPrice = reader["ProductPrice"].ToString(),
                    ProductSales = reader["ProductSales"].ToString(),
                    CategoryID = Convert.ToInt32(reader["CategoryID"]),
                    EnterpriseID = Convert.ToInt32(reader["EnterpriseID"]),
                    Isdelete = Convert.ToInt32(reader["Isdelete"]),
                };
                list.Add(p);
            }
            if (list.Count > 0)
                return list;
            return null;
        }
        public static List<Product> GetProductInfo(int page, int pagesize, out int totalCount, string ProductName)
        {

            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@page",page),
                new SqlParameter("@pageSize",pagesize),
                new SqlParameter("@totalCount",SqlDbType.Int),
                new SqlParameter("@ProductName",ProductName)
            };
            paras[2].Direction = ParameterDirection.Output;
            SqlDataReader reader = DBHelper.ExecuteReader("proc_size1", CommandType.StoredProcedure, paras);
            List<Product> list = new List<Product>();
            while (reader.Read())
            {
                Model.Product p = new Model.Product()
                {
                    ProductID = Convert.ToInt32(reader["ProductID"]),
                    ProductName = reader["ProductName"].ToString(),
                    ProductImage = reader["ProductImage"].ToString(),
                    ProductPrice = reader["ProductPrice"].ToString(),
                    ProductSales = reader["ProductSales"].ToString(),
                    CategoryID = Convert.ToInt32(reader["CategoryID"]),
                    EnterpriseID = Convert.ToInt32(reader["EnterpriseID"]),
                    Isdelete = Convert.ToInt32(reader["Isdelete"]),
                };
                list.Add(p);
            }
            reader.Close();
            totalCount = Convert.ToInt32(paras[2].Value);
            return list;

        }
        public static int XJ(int ProductID)
        {
            string sqltr = "update Product set Isdelete=0 where  ProductID=@ProductID";
            SqlParameter[] paras = new SqlParameter[] {
            new SqlParameter("@ProductID",ProductID)
            };
            int r = DBHelper.ExecuteNonQuery(sqltr, paras);
            return r;
        }
        public static int SJ(int ProductID)
        {
            string sqltr = "update Product set Isdelete=1 where  ProductID=@ProductID";
            SqlParameter[] paras = new SqlParameter[] {
            new SqlParameter("@ProductID",ProductID)
            };
            int r = DBHelper.ExecuteNonQuery(sqltr, paras);
            return r;
        }
        public static int AddProduct(Product pd)
        {
            string sqltr = "insert into Product values(@ProductName,@ProductPrice,@ProductImage,@ProductSales,@CategoryID,@EnterpriseID,@Isdelete)";
            SqlParameter[] paras = new SqlParameter[] {
            new SqlParameter("@ProductName",pd.ProductName),
            new SqlParameter("@ProductPrice",pd.ProductPrice),
            new SqlParameter("@ProductImage",pd.ProductImage),
            new SqlParameter("@ProductSales",pd.ProductSales),
            new SqlParameter("@CategoryID",pd.CategoryID),
            new SqlParameter("@EnterpriseID",pd.EnterpriseID),
            new SqlParameter("@Isdelete",pd.Isdelete),
            };
            return DBHelper.ExecuteNonQuery(sqltr, paras);
        }
    }
}
